/*
 * This class creates all the tables and dummy data in MYSQL for the CBCR project
 */
package mysql;

/**
 *
 * @author Namrata
 */
import java.sql.*;

public class DBCreator {

    public Connection conn;

    public DBCreator() { // To setup connection with MYSQL db
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/cbcr", "cbcruser", "cbcrpwd");
            this.conn = con;
        } catch (Exception e) {
            e.printStackTrace();

        }
    }

    public boolean createTables() {
        try {
            Statement stmt = null;

            if (this.conn != null) {
                stmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);

                //Create student info table
                stmt.executeUpdate("Create table student_info (student_id INT AUTO_INCREMENT,first_name VARCHAR(50) , last_name VARCHAR(50) , address VARCHAR(500), app_date DATE, enrolled_date DATE, GRE_score DOUBLE , TOEFL_score DOUBLE , program VARCHAR(100) , new BOOLEAN, research_interest_area VARCHAR (50) , work_experience_area VARCHAR(50), vote double, educationaInstituteConsideration boolean, PRIMARY KEY (student_id))  ");

                //Create Recommender_info Table
                stmt.executeUpdate("Create table recommender_info (student_id INT , recommender_name VARCHAR(100) , recommended_score DOUBLE,new BOOLEAN,FOREIGN KEY(student_id) references student_info(student_id) ON DELETE CASCADE)");

                //Create Education_info Table
                stmt.executeUpdate("Create table education_info (student_id INT, institute_name VARCHAR(100) , gpa DOUBLE, program VARCHAR(100),major VARCHAR(100), new BOOLEAN,FOREIGN KEY(student_id) references student_info(student_id) ON DELETE CASCADE)");

                //Create Domain Knowledge tables : Univ_rank
                stmt.executeUpdate("Create table univ_rank (univ_name VARCHAR(100) , univ_score DOUBLE)");

                //Create publications table
                stmt.executeUpdate("Create table student_publications (student_id INT ,conference_name VARCHAR(100) , paper_name VARCHAR(500),new BOOLEAN,FOREIGN KEY(student_id) references student_info(student_id) ON DELETE CASCADE)");

                //Create Domain Knowledge tables : Conference_Rank
                stmt.executeUpdate("Create table conference_rank (conference_name VARCHAR(100) , conference_score DOUBLE)");

                //Create Domain Knowledge tables : Proff List
                stmt.executeUpdate("Create table prof_list ( prof_name VARCHAR(100) , prof_vote BOOLEAN)");

                //Create UnivSpecificInfo table which stores the score calculation info

                stmt.executeUpdate("Create table UnivSpecificInfo (  id int NOT NULL auto_increment, school varchar(100), program varchar(50), major varchar(100), GREContriPercent int, TOEFLContriPercent int, EduInstiContriPercent int, GPAContriPercent int, PublicationContriPercent int, RecoContriPercent int, RelExpContriPercent int, Primary Key(id))");


                return true;
            }

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public void insertRows() {
        try {
            Statement stmt = null;

            if (this.conn != null) {
                stmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);


                //Insert univSpecificInfo

                stmt.executeUpdate("Insert into UnivSpecificInfo (school, program, major, GREContriPercent, TOEFLContriPercent, EduInstiContriPercent, GPAContriPercent, PublicationContriPercent,  RecoContriPercent,  RelExpContriPercent) values ('School of Informatics and Computing', 'Masters', 'Computer Science',30, 5, 15, 25, 10, 15, 5)");
                 stmt.executeUpdate("Insert into UnivSpecificInfo (school, program, major, GREContriPercent, TOEFLContriPercent, EduInstiContriPercent, GPAContriPercent, PublicationContriPercent,  RecoContriPercent,  RelExpContriPercent) values ('School of Informatics and Computing', 'PHD', 'Computer Science',20, 5, 10, 25, 15, 15, 10)");
                //Insert into student info table
                
                stmt.executeUpdate(" Insert into student_info (first_name , last_name , address, app_date, enrolled_date, GRE_score,TOEFL_score , program, new,research_interest_area, work_experience_area) values( 'Namrata', 'Lele','Pune - 411004 . India','2007-12-01','2008-09-01',1220,107,'Masters',0,'Databases','Databases')");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(1, 'Sachin Lodha', 8,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(1, 'A A Sawant', 9,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values( 1,'Mrityunjay Prasad', 8,0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 1,'COEP', 8,'B.E Computer',0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 1,'MIT', 9,'Diploma in Computer Engineering',0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 1,'IU', 8,'MS Computer',0)");
                stmt.executeUpdate(" Insert into student_publications (student_id,conference_name , paper_name,new) values(1, 'CIKM', 'XQGen An Algebrabased XPath Query Generator for MicroBenchmarking',0)");
                stmt.executeUpdate(" Insert into student_publications (student_id,conference_name , paper_name,new) values( 1,'HyperText', 'Sixearch.org 2.0 Peer Application for Collaborative Web Search',0)");

                stmt.executeUpdate(" Insert into student_info (first_name , last_name , address, app_date, enrolled_date, GRE_score,TOEFL_score , program, new,research_interest_area, work_experience_area) values( 'Sharwari', 'Jagtap','Pune - 411004 . India','2007-12-01','2008-09-01',1220,107,'Masters',0, null , null)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(2, 'A A Sawant', 9,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(2, 'TYH', 8,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(2, 'Attar', 7,0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values(2, 'COEP', 7.8,'B.E Computer',0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 2,'IU', 9,'MS Computer',0)");
                //stmt.executeUpdate(" Insert into student_publications (student_id,conference_name , paper_name) values(2, null , null)");

                stmt.executeUpdate(" Insert into student_info (first_name , last_name , address, app_date, enrolled_date, GRE_score,TOEFL_score , program, new,research_interest_area, work_experience_area) values( 'Rohan', 'Seth','Mumbai . India','2007-12-01','2008-09-01',1350,111,'Masters',0, 'Networks','Databases')");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(3, 'RER', 8,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values( 3,'RTTR', 8,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values( 3,'YUG', 8,0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 3,'RTT', 8.2,'B.E Electronics',0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 3,'IU',7,'MS Computer',0)");
                //stmt.executeUpdate(" Insert into student_publications (conference_name , paper_name) values( null , null)");

                stmt.executeUpdate(" Insert into student_info (first_name , last_name , address, app_date, enrolled_date, GRE_score,TOEFL_score , program, new,research_interest_area, work_experience_area) values( 'Kartika', 'Kale','Pune - 411004 . India','2007-12-01','2008-09-01',1400,105,'Masters',0,null,null)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(4, 'ERT', 10,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(4, 'VBB', 8,0)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(4, 'UIU', 8,0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 4,'FGFGH', 9,'B.E Computer',0)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 4,'IU', 7,'MS Computer',0)");
                //stmt.executeUpdate(" Insert into student_publications (conference_name , paper_name) values( null , null)");

                stmt.executeUpdate(" Insert into student_info (first_name , last_name , address, app_date, enrolled_date, GRE_score,TOEFL_score , program, new,research_interest_area, work_experience_area) values( 'ART', 'SCI','INDIA','2003-12-01','2004-09-01',1200,105,'Masters',1,'Networks','Networks')");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(5, 'A A Sawant', 9,1)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(5, 'VBB', 8,1)");
                stmt.executeUpdate(" Insert into recommender_info (student_id,recommender_name , recommended_score,new) values(5, 'UIU', 8,1)");
                stmt.executeUpdate(" Insert into education_info (student_id,institute_name , gpa, program,new) values( 5,'COEP', 7,'B.E Computer',1)");
                //stmt.executeUpdate(" Insert into student_publications (conference_name , paper_name) values( null , null)");
             
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
    }

    /**
     * @param args the command line arguments
     */
    /*public static void main(String[] args) {
        // TODO code application logic here

        DBCreator dbc = new DBCreator();
        dbc.createTables();
        dbc.insertRows();
    }*/
}

